﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Drawing.Drawing2D;
using Chart2DLib;

namespace WindowsFormsApplication1
{
    public partial class form_dependence1 : Form
    {
        string NUMBER_experiment;
        string q;
       
       
        DataSet ds;
        OleDbDataAdapter da;
        private DataSeries dpoint; // для коллекции графиков
      
        public form_dependence1()
        {
            InitializeComponent();
        }
       
        private void connect_to_db(string query) 
        {
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
            ds = new DataSet();
            da = new OleDbDataAdapter(query, conn);
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(ds, "table_dependence");

            grid_dependence.DataSource = ds.Tables["table_dependence"]; 
            grid_dependence.Columns[0].Visible = false;
            grid_dependence.Columns[1].Visible = false;
            grid_dependence.Sort(grid_dependence.Columns[0], ListSortDirection.Ascending);
        }

        public void Show_dependence(String number_experiment, String name_experiment, String ustanovka, String type_analysis, String sample, String date_experiment)
        {
            NUMBER_experiment = number_experiment;

            lbl_name_experiment.Text = name_experiment;
            lbl_ustanovka.Text = ustanovka;
            lbl_type_analysis.Text = type_analysis;
            lbl_sample.Text = sample;
            lbl_date.Text = date_experiment;
           
            q = "[Номер эксперимента], ";
            switch (ustanovka)
            {
                case "Teclis Foamscan":
                    switch (type_analysis)
                    {
                        case "Высота пены":
                            q += "[Высота пены], Время";
                            break;
                        case "Распределение дисперсности":
                            q += "[Распределение дисперсности], Температура, Давление, [Тип жидкости]";
                            break;
                        case "Кратность пены":
                            q += "[Кратность пены], Температура, [Тип жидкости]";
                            break;
                    }
                    break;
                case "Установка собственного производства":
                    q += "[Высота пены], Температура, Давление";
                    break;
                case "PSL Systemtechnik DSL":
                    q += "[Перепад давлений], Температура, Давление, [Тип жидкости]";
                    break;
                case "PSL Systemtechnik FT5":
                    q += "[Точка начала выпадения асфальтенов], Температура, Давление, [Тип жидкости]";
                    break;
                case "PSL Systemtechnik Wax Loop":
                    switch (type_analysis)
                    {
                        case "Перепад давлений":
                            q += "[Перепад давлений], Температура, Давление, [Тип жидкости]";
                            break;
                        case "Перепад температур":
                            q += "[Перепад температур], Температура, Давление, [Тип жидкости]";
                            break;
                    }
                    break;
                case "PSL Systemtechnik GHA 200":
                    switch (type_analysis)
                    {
                        case "Перепад давлений":
                            q += "[Перепад давлений], Температура, Давление, [Тип жидкости]";
                            break;
                        case "Перепад температур":
                            q += "[Перепад температур], Температура, Давление, [Тип жидкости]";
                            break;
                    }
                    break;
                case "QV":
                    q += "Вязкость, [Скорость сдвига]";
                    break;
                case "PSL Systemtechnik VT 550":
                    q += "Вязкость, Температура, [Скорость сдвига], [Напряжение сдвига]";
                    break;
                case "Anton Paar DMA 450":
                    q += "Плотность, Температура, Давление";
                    break;
                case "Krüss BP100":
                    q += "[Поверхностное натяжение], Температура";
                    break;
                case "Krüss DVT50":
                    q += "Плотность, Время, Температура";
                    break;
                case "Dispersion DT-1201":
                    switch (type_analysis)
                    {
                        case "Объёмная доля частиц":
                            q += "[Объёмная доля частиц], Температура";
                            break;
                        case "Распределение дисперсности":
                            q += "[Распределение частиц по размеру], Температура";
                            break;
                        case "Дзета-потенциал":
                            q += "[Дзета-потенциал], Температура";
                            break;
                    }
                    break;
            }
            connect_to_db("SELECT " + "[Код], " + q + " FROM table_dependence WHERE [Номер эксперимента]=" + NUMBER_experiment);
            cmb_select_variable.Items.Clear();
            for (int i = 3; i <= grid_dependence.ColumnCount - 1 ; i++) 
            {
                if (grid_dependence.Columns[i].HeaderText != "Тип жидкости")
                {
                    cmb_select_variable.Items.Add(grid_dependence.Columns[i].HeaderText);
                }
            }
            cmb_select_variable.SelectedIndex = 0;
            chart_draw();
        }
      
        private void insert_dependence()
        {
            string Value_variables = ") VALUES('";
            for (int i = 1; i <= (ds.Tables["table_dependence"].Columns.Count - 1); i++)
            {
                if (Value_variables != ") VALUES('")
                {
                    Value_variables += "','";
                }
                if (grid_dependence[i, grid_dependence.RowCount - 1].Value.ToString() != "")
                {
                    Value_variables += grid_dependence[i, grid_dependence.RowCount - 1].Value.ToString();
                }
                else
                {
                    Value_variables += "0";
                }
            }
            Value_variables += "')";

            connect_to_db("SELECT " + "[Код], " + q + " FROM table_dependence WHERE [Номер эксперимента]=" + NUMBER_experiment);
            DataRow row = ds.Tables["table_dependence"].NewRow();
            ds.Tables["table_dependence"].Rows.Add(row);

            OleDbCommand InsertCommand = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
            InsertCommand.CommandText = @"INSERT INTO [table_dependence] (" + q + Value_variables;
            InsertCommand.Connection = conn;
            da.InsertCommand = InsertCommand;
            da.Update(ds, "table_dependence");

            connect_to_db("SELECT " + "[Код], " + q + " FROM table_dependence WHERE [Номер эксперимента]=" + NUMBER_experiment);
        }
       
        private void update_dependence(int j)
        {
          
            ds.Tables["table_dependence"].AcceptChanges();

            grid_dependence.ScrollBars = ScrollBars.None;
           
                ulong cod = System.Convert.ToUInt64(grid_dependence[0, j].Value);
                string Value_variables = "";
               
                for (int i = 1; i <= (grid_dependence.Columns.Count - 1); i++)
                {
                    if (Value_variables != "")
                    {
                        Value_variables += ", ";
                    }
                    Value_variables += "["+ grid_dependence.Columns[i].HeaderText+"]='";
                    if (grid_dependence[i, j].Value.ToString() != "")
                    {               
                        Value_variables += grid_dependence[i, j].Value.ToString() + "'" ;
                    }
                    else
                    {
                        Value_variables += "0'";
                    }
                }
               
                DataRow row = ds.Tables["table_dependence"].NewRow();
                ds.Tables["table_dependence"].Rows.Add(row);

                OleDbCommand InsertCommand = new OleDbCommand();
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
                InsertCommand.CommandText = @"UPDATE [table_dependence] SET " + Value_variables + " WHERE Код=" + cod + "";
                InsertCommand.Connection = conn;
                da.InsertCommand = InsertCommand;
                da.Update(ds, "table_dependence");
            
            connect_to_db("SELECT " + "[Код], " + q + " FROM table_dependence WHERE [Номер эксперимента]=" + NUMBER_experiment);
            grid_dependence.ScrollBars = ScrollBars.Both;
        }

        private void delete_dependence()
        {
            ulong cod = System.Convert.ToUInt64(grid_dependence[0, grid_dependence.CurrentRow.Index].Value);
            OleDbCommand InsertCommand = new OleDbCommand();
            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\pav.mdb");
            InsertCommand.CommandText = @"DELETE FROM [table_dependence] WHERE Код=" + cod + "";
            InsertCommand.Connection = conn;
            conn.Open();
            InsertCommand.ExecuteNonQuery();
            conn.Close();
            connect_to_db("SELECT " + "[Код], " + q + " FROM table_dependence WHERE [Номер эксперимента]=" + NUMBER_experiment);
        }

        private void chart_draw()
        {
            
            if (grid_dependence.RowCount > 1)
            {
                tab_control.TabPages[1].Visible = true;
                grid_dependence.Sort(grid_dependence.Columns[3 + cmb_select_variable.SelectedIndex], ListSortDirection.Ascending);
                dpoint = new DataSeries();
                dpoint.LineStyle.LineColor = Color.Red;
                dpoint.LineStyle.Thickness = 2f;
                dpoint.LineStyle.Pattern = DashStyle.Solid;
                dpoint.LineStyle.PlotMethod =
                LineStyle.PlotLinesMethodEnum.Splines;
                dpoint.SeriesName = "Т";
                dpoint.SymbolStyle.SymbolType =
                SymbolStyle.SymbolTypeEnum.Dot;
                dpoint.SymbolStyle.BorderColor = Color.Blue;
                float oX, oY, minoX = 0, minoY = 0, maxoX = 0, maxoY = 0;
                for (int i = 0; i <= grid_dependence.RowCount - 1; i++)
                {
                    oX = System.Convert.ToSingle(grid_dependence[3 + cmb_select_variable.SelectedIndex, i].Value.ToString());
                    oY = System.Convert.ToSingle(grid_dependence[2, i].Value.ToString());
                    if (i == 0)
                    {
                        minoX = oX;
                        minoY = oY;
                        maxoX = oX;
                        maxoY = oY;
                    }
                    else
                    {
                        minoX = Math.Min(minoX, oX);
                        maxoX = Math.Max(maxoX, oX);
                        minoY = Math.Min(minoY, oY);
                        maxoY = Math.Max(maxoY, oY);
                    }
                    dpoint.AddPoint(new PointF(oX, oY));
                }
                chart.C2Label.YLabel = grid_dependence.Columns[2].HeaderText;
                chart.C2Label.XLabel = cmb_select_variable.Text;
                chart.C2XAxis.XLimMin = System.Convert.ToSingle(minoX - 0.1 * (maxoX - minoX));
                chart.C2XAxis.XLimMax = System.Convert.ToSingle(maxoX + 0.1 * (maxoX - minoX));
                chart.C2XAxis.XTick = System.Convert.ToSingle(0.1 * (maxoX + 0.1 * (maxoX - minoX) - minoX - 0.1 * (maxoX - minoX)));
                chart.C2YAxis.YLimMin = System.Convert.ToSingle(minoY - 0.1 * (maxoY - minoY));
                chart.C2YAxis.YLimMax = System.Convert.ToSingle(maxoY + 0.1 * (maxoY - minoY));
                chart.C2YAxis.YTick = System.Convert.ToSingle(0.1 * (maxoY + 0.1 * (maxoY - minoY) - minoY - 0.1 * (maxoY - minoY)));
                chart.C2DataCollection.RemoveAll();
                chart.C2DataCollection.Add(dpoint);
                chart.Refresh();
                grid_dependence.Sort(grid_dependence.Columns[0], ListSortDirection.Ascending);
            }
            else
            {
                tab_control.TabPages[1].Visible = false;
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ds.Tables["table_dependence"].AcceptChanges();
        }

        private void grid_dependence_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            update_dependence(grid_dependence.CurrentRow.Index);
        }

        private void cmb_select_variable_SelectedIndexChanged(object sender, EventArgs e)
        {
            chart_draw();
        }

        private void tab_control_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (tab_control.SelectedIndex == 1)
            {
                chart_draw();
            }
        }

        private void add_row_Click(object sender, EventArgs e)
        {
            DataRow row = ds.Tables["table_dependence"].NewRow();
            row["Номер эксперимента"] = NUMBER_experiment.ToString();
            ds.Tables["table_dependence"].Rows.Add(row);
            grid_dependence.DataSource = ds.Tables["table_dependence"];
            insert_dependence();
            chart_draw();
        }

        private void delete_row_Click(object sender, EventArgs e)
        {
            delete_dependence();
            chart_draw();
        }

    }
}
